package com.asiainfo.demo.dao.impl;

import com.asiainfo.demo.dao.idao.iMember;
import com.asiainfo.demo.dao.roles.Member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MemberImpl implements iMember {
    private Connection con = null;
    private PreparedStatement ps = null;

    public MemberImpl(Connection con){
        this.con = con;
    }

    @Override
    public boolean insertMember(Member member) throws SQLException {
        String sql = "INSERT INTO MEMBER(MEM_NAME, MEM_ADDR, MEM_JOB, MEM_VALID) VALUES (?, ?, ?, ?);";

        ps = con.prepareStatement(sql);
        ps.setString(1, member.getMemName());
        ps.setString(2, member.getMemAddr());
        ps.setString(3, member.getMemJob());
        ps.setByte(4, member.getMemValid());

        return ps.executeUpdate() > 0;
    }

    @Override
    public ArrayList<Member> findMemberByID(int ID) throws SQLException {
        String sql = "SELECT * FROM MEMBER WHERE MEM_ID = ?;";

        ps = con.prepareStatement(sql);
        ps.setInt(1, ID);

        return resultSetToMember(ps);
    }

    @Override
    public ArrayList<Member> findMemberByName(String name) throws SQLException {
        String sql = "SELECT * FROM MEMBER WHERE MEM_NAME LIKE ?;";

        ps = con.prepareStatement(sql);
        ps.setString(1, name);

        return resultSetToMember(ps);
    }

    @Override
    public ArrayList<Member> findMemberByJob(String job) throws SQLException {
        String sql = "SELECT * FROM MEMBER WHERE MEM_JOB LIKE ?;";

        ps = con.prepareStatement(sql);
        ps.setString(1, job);

        return resultSetToMember(ps);
    }

    @Override
    public ArrayList<Member> findMemberByAddr(String addr) throws SQLException {
        String sql = "SELECT * FROM MEMBER WHERE MEM_ADDR LIKE ?;";

        ps = con.prepareStatement(sql);
        ps.setString(1, addr);

        return resultSetToMember(ps);
    }

    @Override
    public ArrayList<Member> findAllMember() throws SQLException {
        String sql = "SELECT * FROM MEMBER;";

        ps = con.prepareStatement(sql);

        return resultSetToMember(ps);
    }

    @Override
    public boolean changeValid(int memID, byte newStatus) throws SQLException {
        String sql = "UPDATE MEMBER SET MEM_VALID = ? WHERE MEM_ID = ?;";

        ps = con.prepareStatement(sql);

        ps.setByte(1, newStatus);
        ps.setInt(2, memID);

        return ps.executeUpdate() > 0;
    }

    private ArrayList<Member> resultSetToMember(PreparedStatement ps) throws SQLException{
        ResultSet resultSet = ps.executeQuery();
        ArrayList<Member> resMembers = new ArrayList<>();

        while (resultSet.next()){
            Member member = new Member();
            member.setMemID(resultSet.getInt("MEM_ID"));
            member.setMemAddr(resultSet.getString("MEM_ADDR"));
            member.setMemName(resultSet.getString("MEM_NAME"));
            member.setMemValid(resultSet.getByte("MEM_VALID"));
            member.setMemJob(resultSet.getString("MEM_JOB"));

            resMembers.add(member);
        }

        return resMembers;
    }
}
